Chapter 7 — Data Cleaning and Preparation
Code Reference File — Copy and paste as needed

========================================
Setup
========================================
from google.colab import drive
drive.mount('/content/drive')
import pandas as pd
file_path = '/content/drive/MyDrive/sales_data.xlsx'
countries = pd.read_excel(file_path, sheet_name='Countries')
product   = pd.read_excel(file_path, sheet_name='Product')
sales     = pd.read_excel(file_path, sheet_name='Sales')

========================================
7.1.1 Drop Unneeded Columns
========================================
sales = sales.drop(columns=['Urgent?'], errors='ignore')

========================================
7.1.2 Split Column
========================================
product[['Product Type', 'Product Name']] = product['Product Type/Product'].str.split('/', n=1, expand=True)
product['Product Type'] = product['Product Type'].str.strip()
product['Product Name'] = product['Product Name'].str.strip()

========================================
7.1.3 Remove Rows with Missing Values
========================================
sales = sales[sales['Quantity Sold'].apply(lambda x: not pd.isnull(x))]

========================================
7.1.5 Remove Duplicates
========================================
countries = countries.drop_duplicates()

========================================
7.1.6 Add Calculated Columns
========================================
sales = sales.rename(columns={'Value': 'Sale Price'})
sales['Revenue'] = sales['Sale Price'] * sales['Quantity Sold']

========================================
7.2.1 Rename Columns
========================================
sales = sales.rename(columns={'RetCity': 'Retailer City', 'Trans Date': 'Date'})

========================================
7.2.2 Set Data Types
========================================
sales['Date']              = pd.to_datetime(sales['Date']).dt.date
sales['Sale Price']        = sales['Sale Price'].astype(float)
sales['Quantity Sold']     = sales['Quantity Sold'].astype(int)
sales['Order Method Type'] = sales['Order Method Type'].astype(str)
sales['Retailer City']     = sales['Retailer City'].astype(str)
sales['Retailer Type']     = sales['Retailer Type'].astype(str)

========================================
7.3.1 Fix Retailer Type
========================================
sales['Retailer Type'] = sales['Retailer Type'].replace({'Dept. store': 'Department Store', 'Direct Mark.': 'Direct Marketing'})
sales['Retailer Type'] = sales['Retailer Type'].str.strip()

========================================
7.3.2 Fix Spelling Mistakes
========================================
countries['City'] = countries['City'].replace({'Londonn': 'London'})

========================================
7.3.3 Forward Fill
========================================
product['Product Line'] = product['Product Line'].ffill()

========================================
7.3.5 Change Case
========================================
product['Product Name'] = product['Product Name'].str.title()

========================================
7.4 Method Chaining
========================================
sales_clean = (
    sales
    .dropna(subset=['Quantity Sold'])
    .replace({'Retailer Type': {'Dept. store': 'Department Store', 'Direct Mark.': 'Direct Marketing'}})
    .assign(Retailer_Type=lambda df: df['Retailer Type'].str.strip())
    .drop_duplicates()
    .reset_index(drop=True)
)
print(sales_clean.head())
